package com.gaotu.demo.jdbctemplate;

import com.gaotu.demo.User;
import com.gaotu.demo.UserDao;

import com.gaotu.demo.UserStatus;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.Date;
import java.util.List;

@Component
public class JdbcTemplateBasedUserDao implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void insert(User user) {
        String sql = "insert into " +
                "tb_user(`create_time`,`update_time`,`status`,`name`, `password`)" +
                " values " +
                "(?, ?, ?, ?, ?)";
        PreparedStatementCreator preparedStatementCreator = connection -> {
            PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setDate(1, toSqlDate(user.getCreateTime()));
            preparedStatement.setDate(2, toSqlDate(user.getUpdateTime()));
            preparedStatement.setInt(3, user.getStatus().getCode());
            preparedStatement.setString(4, user.getName());
            preparedStatement.setString(5, user.getPassword());
            return preparedStatement;
        };
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(preparedStatementCreator, keyHolder);
        user.setId(keyHolder.getKey().longValue());

    }

    @Override
    public void update(User user) {
        String sql ="update tb_user " +
                " set " +
                "update_time = ?," +
                "status = ?," +
                "name = ?, " +
                "password = ?"+
                " where " +
                "id = ?";

        PreparedStatementSetter preparedStatementSetter = preparedStatement -> {
            preparedStatement.setDate(1, toSqlDate(user.getUpdateTime()));
            preparedStatement.setInt(2, user.getStatus().getCode());
            preparedStatement.setString(3, user.getName());
            preparedStatement.setString(4, user.getPassword());
            preparedStatement.setLong(5, user.getId());
        };

        jdbcTemplate.update(sql, preparedStatementSetter);
    }

    @Override
    public void deleteById(Long id) {
        String sql = "delete from " +
                "tb_user" +
                " where " +
                "id = ?";
        PreparedStatementSetter preparedStatementSetter = preparedStatement -> {
            preparedStatement.setLong(1, id);
        };

        jdbcTemplate.update(sql, preparedStatementSetter);

    }

    @Override
    public User getById(Long id) {
        String sql ="select id, create_time, update_time, status, name, password " +
                "from tb_user  where id = ?";
        List<User> result = jdbcTemplate.query(sql, new Object[]{id},
                (resultSet, i) -> {
                    Long idForSelect = resultSet.getLong(1);
                    java.sql.Date createDate = resultSet.getDate(2);
                    java.sql.Date updateDate = resultSet.getDate(3);
                    Integer statusCode = resultSet.getInt(4);
                    String name = resultSet.getString(5);
                    String password = resultSet.getString(6);
                    User user = new User();
                    user.setId(idForSelect);
                    user.setCreateTime(createDate);
                    user.setUpdateTime(updateDate);
                    user.setStatus(UserStatus.valueOfCode(statusCode));
                    user.setName(name);
                    user.setPassword(password);
                    return user;
                });
        return result.isEmpty() ? null : result.get(0);
    }

    @Override
    public User getByName(String name) {
        String sql = "select " +
                "id, create_time, update_time, status, name, password " +
                "from tb_user " +
                "where " +
                "name = ?";
        List<User> result = jdbcTemplate.query(sql, new Object[]{name}, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {

                Long idForSelect = resultSet.getLong(1);
                java.sql.Date createDate = resultSet.getDate(2);
                java.sql.Date updateDate = resultSet.getDate(3);
                Integer statusCode = resultSet.getInt(4);
                String name = resultSet.getString(5);
                String password = resultSet.getString(6);
                User user = new User();
                user.setId(idForSelect);
                user.setCreateTime(createDate);
                user.setUpdateTime(updateDate);
                user.setStatus(UserStatus.valueOfCode(statusCode));
                user.setName(name);
                user.setPassword(password);
                return user;
            }
        });
        return result.isEmpty() ? null : result.get(0);
    }

    private java.sql.Date toSqlDate(Date date) {
        return new java.sql.Date(date.getTime());
    }
}
